package DBLayer;


import java.util.ArrayList;
import java.sql.*;

import ModelLayer.*;

public class DBCustomer implements IFDBCustomer{
	
	private Connection con;
	
	public DBCustomer(){
		con = DbConnection1.getInstance().getDBcon();
		}
	
	
	//Method to get all customers
	
	public ArrayList<Customer> getAllCustomer(boolean retrieveAssociation) {
		
		return miscWhere("", retrieveAssociation);
	}

	//Method to find a specific customer
	
	public Customer findCustomer(String cprNo, boolean retrieveAssociation) {
		String wClause = " cpr_No = '" + cprNo + "'";
		return singleWhere(wClause, retrieveAssociation);
		
	}

	//@Override
	public int insertCustomer(Customer customer) throws Exception {
		
		int rc = -1;
		
		String query = "INSERT INTO customer (cpr_No, fname, lname, address, pcode, phoneno, club_id) VALUES('" + 
				customer.getCprNo() + "', '" +
				customer.getFName() + "', '" +
				customer.getlName() + "', '" +
				customer.getAddress() + "', " +
				customer.getZipCode().getpCode() +  ",'" +
				customer.getPhoneNo() + "', " +
				customer.getClub().getClubId() + ")" ;
		
		System.out.println("insert : " + query);
		
		try{Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(query);
				stmt.close();
				}catch(SQLException ex){
					System.out.println("customer does not exist");
					throw new Exception ("insertion was not correct");
				}
		
		return rc;
	}

	@Override
	public int updateCustomer(Customer customer) {
		
		Customer custObj  = customer;
		int rc=-1;
	  
		String query="UPDATE customer SET "+
		 	  "fname ='"+ custObj.getFName() +"', "+
		 	  "lname ='"+ custObj.getlName() + "', " +
		 	  "address ='"+ custObj.getAddress() + "', "+
		 	  "pcode ='" + Integer.toString(custObj.getZipCode().getpCode()) + "', "+
		 	  "phoneno ='" + custObj.getPhoneNo() + "', "+
		 	  "club_id ='" + Integer.toString(custObj.getClub().getClubId()) + "' "+
		          " WHERE cpr_No = '"+ custObj.getCprNo() + "'";
                System.out.println("Update query:" + query);
  		try{ // update employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);
                      
	 	 	stmt.close();
		}//end try	
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in employee db: "+ex);
	  	}
		return(rc);
	}
	
	
	public int delete(Customer customer)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM customer WHERE cpr_no = '" +
				customer.getCprNo() + "'";
                System.out.println(query);
	  	try{ // delete from employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in customer db: "+ex);
   	        }
		return(rc);
	}
	
	

	
	
	private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Customer> list = new ArrayList<Customer>();	
		
		String query =  buildQuery(wClause);
  
  		try{ // read the employee from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		

			while( results.next() ){
				
		     	 Customer custObj = new Customer();
			 custObj = buildCustomer(results);	
			 
			
                 
                 //association is to be build
                
                 
                 if(retrieveAssociation)
                 {   //The zipCode and Club are to be build as well
                    
                	 
                	 DBZipCode dBzipObj = new DBZipCode();
                	 ZipCode zip = dBzipObj.findZipCode(custObj.getZipCode().getpCode(), false);
                	 custObj.setZipCode(zip);
                	 System.out.println("Zip code is selected");
                	 
                	 DBClub dBclubObj = new DBClub();
                	 Club club = dBclubObj.findClub(custObj.getClub().getClubId(), false);
                	 custObj.setClub(club);
                	 System.out.println("Club is selected");
                	 
                 }
                 
                         list.add(custObj);	
			
                        
			stmt.close();
		}
  		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	 
	
	
	
	
	
	//SingleWhere is used when we only select one customer  
	
	 private Customer singleWhere(String wClause, boolean retrieveAssociation)
	  {
	   ResultSet results;
	   Customer custObj = new Customer();
	   custObj.setCprNo("");
	   	String query =  buildQuery(wClause);
	                 System.out.println(query);
	   try{ // read the employee from the database
	     Statement stmt = con.createStatement();
	     stmt.setQueryTimeout(5);
	     results = stmt.executeQuery(query);
	     
	     if( results.next() ){
	                             custObj = buildCustomer(results);
	                             //association is to be build
	                            
	                             stmt.close();
	                             if(retrieveAssociation)
	                             {   //The zipCode and Club are to be build as well
	                                
	                            	 
	                            	 DBZipCode dBzipObj = new DBZipCode();
	                            	 ZipCode zip = dBzipObj.findZipCode(custObj.getZipCode().getpCode(), false);
	                            	 custObj.setZipCode(zip);
	                            	 System.out.println("Zip code is selected");
	                            	 
	                            	 DBClub dBclubObj = new DBClub();
	                            	 Club club = dBclubObj.findClub(custObj.getClub().getClubId(), false);
	                            	 custObj.setClub(club);
	                            	 System.out.println("Club is selected");
	                            	 
	                             }
	     			}
	                         else{ //no customer was found
	                             custObj = null;
	                         }
	   }//end try 
	    catch(Exception e){
	     System.out.println("Query exception: "+e);
	    }
	   return custObj;
	  }
	 
	 
	 
	 //method to build a query
	 private String buildQuery(String wClause)
	 {
	     String query="SELECT cpr_No, fname, lname, address, pcode, phoneno, club_id FROM customer";
	  
	  if (wClause.length()>0)
	   query=query+" WHERE "+ wClause;
	   
	  return query;
	 }
	
	 
	
	 
	 private Customer buildCustomer(ResultSet results)
     {   Customer custObj = new Customer();
         try{ // the columns from the table emplayee  are used
        	   custObj.setCprNo(results.getString("cpr_No"));
               custObj.setFName(results.getString("fname"));
               custObj.setLName(results.getString("lname"));
               custObj.setAddress(results.getString("address"));
               custObj.setZipCode(new ZipCode(results.getInt("pcode")));
               custObj.setPhoneNo(results.getString("phoneno"));
               custObj.setClub(new Club(results.getInt("club_id")));
               
                 }
        catch(Exception e)
        {
            System.out.println("error in building the customer object");
        }
        return custObj;
     }



	
	

	
	
}//end class
